﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DDF.Data.Providers;
using DDF.Data;
using System.Data;
using System.Data.SqlClient;
using ARR.Entities.Gateways;

namespace ARR.DataModel.Repositories
{
    public static partial class ArticleSalesRepository
    {
        public static void UpdateFromTempArticleSales()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("ArticleSales_UpdateFromTempArticleSales", CommandType.StoredProcedure);
                database.ExecuteNonQuery();
            }
        }

        public static void InsertFromTempArticleSales()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("ArticleSales_InsertFromTempArticleSales", CommandType.StoredProcedure);                
                database.ExecuteNonQuery();
            }
        }

        public static void UpdateFromTempPIDifferences()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 300;

                database.NewCommand("ArticleSales_UpdateFromTempPIDifferences", CommandType.StoredProcedure);
                database.ExecuteNonQuery();
            }
        }
        

        public static List<ArticleSaleRow> SelectByRangeReview(int rangeReviewId, int? categoryId = null)
        {
            List<ArticleSaleRow> sales = new List<ArticleSaleRow>();
            
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("ArticleSales_SelectByRangeReview", CommandType.StoredProcedure);
                database.AddInParameter("RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("CategoryId", DbType.Int32, categoryId ?? 0);
                
                IDataReader reader = database.ExecuteReader();

                LoadSales(reader, sales);
            }

            return sales;
        }

        public static List<CategoryMarginRow> SelectCategoryMargins(int rangeReviewId)
        {
            List<CategoryMarginRow> margins = new List<CategoryMarginRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("ArticleSales_CategoryMargins", CommandType.StoredProcedure);
                database.AddInParameter("RangeReviewId", DbType.Int32, rangeReviewId);

                IDataReader reader = database.ExecuteReader();

                LoadCategoryMargins(reader, margins);
            }

            return margins;        
        }

        private static void LoadCategoryMargins(IDataReader reader, List<CategoryMarginRow> margins)
        {
            CategoryMarginRow row;

            while (reader.Read())
            {
                row = new CategoryMarginRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Margin = Util.CastDbValue<decimal?>(reader["Margin"])
                };

                margins.Add(row);
            }

            reader.Close();
        }

        private static void LoadSales(IDataReader reader, List<ArticleSaleRow> ArticleSaleList)
        {
            ArticleSaleRow sale;

            while (reader.Read())
            {
                sale = new ArticleSaleRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    StoreId = Util.CastDbValue<int>(reader["StoreId"]),
                    ArticleId = Util.CastDbValue<int>(reader["ArticleId"]),
                    Date = Util.CastDbValue<DateTime>(reader["Date"]),
                    Units = Util.CastDbValue<int>(reader["Units"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    GM = Util.CastDbValue<decimal>(reader["GM"]),
                    GrossSales = Util.CastDbValue<decimal>(reader["GrossSales"]),
                    Price = Util.CastDbValue<decimal?>(reader["Price"]),
                    Confirmed = Util.CastDbValue<bool?>(reader["Confirmed"]),

                    ArticleName = Util.CastDbValue<string>(reader["ArticleName"]),
                    ArticleCode = Util.CastDbValue<string>(reader["ArticleCode"]),
                    UOM = Util.CastDbValue<string>(reader["UOM"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    Subcategory = Util.CastDbValue<string>(reader["Subcategory"]),
                    StoreName = Util.CastDbValue<string>(reader["StoreName"])
                };

                ArticleSaleList.Add(sale);
            }

            reader.Close();
        }
    }
}
